﻿
-- 获取门禁机最新权限信息
CREATE procedure [dbo].[MjjAuthority_Get]
@sblsh int = null
as
select kh,sfhbmd,sfkqfq,yxjcsj,aa.sblsh,0 zt,ryid rypin,Ry_Info.xm,gly,zw1,zw2,zw3,zw4,zw5,zw6,zw7,zw8,zw9,zw10,mbsj,Ry_Info.sfzh,Ry_Info.xb,Ry_Info.gh,gz1 sjfl,bzmc ejfl,fbmc yjfl from vwFetchChangeUserAuthority aa
inner join JsdRyqx_Info on(aa.jsdbh=JsdRyqx_Info.jsdbh and aa.sfzh=JsdRyqx_Info.sfzh and zt=0)
inner join Jsdsb_Info on(aa.jsdbh=Jsdsb_Info.jsdbh and aa.sblsh=Jsdsb_Info.sblsh)
inner join Ry_Info on(JsdRyqx_Info.sfzh=Ry_Info.sfzh)
left join RyPrint_Info on(Ry_Info.sfzh=RyPrint_Info.sfzh)
left join RyFace_Info on(Ry_Info.sfzh=RyFace_Info.sfzh)
left join Bzry_Info on(Ry_Info.sfzh=Bzry_Info.sfzh)
left join Banz_Info on(Bzry_Info.bzid=Banz_Info.bzid)
left join Fenbry_Info on(Ry_Info.sfzh=Fenbry_Info.sfzh)
left join Fenb_Info on(Fenbry_Info.fbid=Fenb_Info.fbid)
where @sblsh is null or aa.sblsh=@sblsh
union
select kh,sfhbmd,sfkqfq,yxjcsj,aa.sblsh,1 zt,ryid rypin,Ry_Info.xm,gly,zw1,zw2,zw3,zw4,zw5,zw6,zw7,zw8,zw9,zw10,mbsj,Ry_Info.sfzh,Ry_Info.xb,Ry_Info.gh,gz1 sjfl,bzmc ejfl,fbmc yjfl from vwFetchChangeUserAuthority aa
inner join JsdRyqx_Info on(aa.jsdbh=JsdRyqx_Info.jsdbh and aa.sfzh=JsdRyqx_Info.sfzh and zt=1)
inner join Jsdsb_Info on(aa.jsdbh=Jsdsb_Info.jsdbh and aa.sblsh=Jsdsb_Info.sblsh)
inner join Ry_Info on(JsdRyqx_Info.sfzh=Ry_Info.sfzh)
left join RyPrint_Info on(Ry_Info.sfzh=RyPrint_Info.sfzh)
left join RyFace_Info on(Ry_Info.sfzh=RyFace_Info.sfzh)
left join Bzry_Info on(Ry_Info.sfzh=Bzry_Info.sfzh)
left join Banz_Info on(Bzry_Info.bzid=Banz_Info.bzid)
left join Fenbry_Info on(Ry_Info.sfzh=Fenbry_Info.sfzh)
left join Fenb_Info on(Fenbry_Info.fbid=Fenb_Info.fbid)
where @sblsh is null or aa.sblsh=@sblsh
union
select kh,sfhbmd,sfkqfq,yxjcsj,aa.sblsh,2 zt,ryid rypin,Ry_Info.xm,gly,zw1,zw2,zw3,zw4,zw5,zw6,zw7,zw8,zw9,zw10,mbsj,Ry_Info.sfzh,Ry_Info.xb,Ry_Info.gh,gz1 sjfl,bzmc ejfl,fbmc yjfl from vwFetchChangeUserAuthority aa
inner join Sb_Info on(Sb_Info.sblsh=aa.sblsh)
inner join SbDqyh_Info on(aa.jsdbh=SbDqyh_Info.jsdbh and aa.sfzh=SbDqyh_Info.sfzh and aa.sblsh=SbDqyh_Info.sblsh and (zt=2 or (zt=1 and sblx<>9))) -- 本公司门禁机有修改功能，需过滤
inner join Ry_Info on(SbDqyh_Info.sfzh=Ry_Info.sfzh)
left join RyPrint_Info on(Ry_Info.sfzh=RyPrint_Info.sfzh)
left join RyFace_Info on(Ry_Info.sfzh=RyFace_Info.sfzh)
left join Bzry_Info on(Ry_Info.sfzh=Bzry_Info.sfzh)
left join Banz_Info on(Bzry_Info.bzid=Banz_Info.bzid)
left join Fenbry_Info on(Ry_Info.sfzh=Fenbry_Info.sfzh)
left join Fenb_Info on(Fenbry_Info.fbid=Fenb_Info.fbid)
where @sblsh is null or aa.sblsh=@sblsh

--select * from dbo.Sb_Info